<?php if (!defined('BASEPATH')) exit('No direct script access allowed');
class ModelDatabase extends MY_AdminModel
{
    function __construct()
    {
        parent::__construct();
    }

    private function _filterSessionTable(&$listTable)
    {
        if (!is_array($listTable)) return;
        if (FALSE !== ($session_table_name_index = array_search($this->config->item('sess_table_name'), $listTable))) {
            unset($listTable[$session_table_name_index]);
        }
    }

    public function sync($userId = '1')
    {
        $this->load->database();
        $listTable = $this->db->list_tables();
        $this->_filterSessionTable($listTable);
        $this->db->where_not_in($this->map->table()->Name, $listTable)->delete($this->map->table);
        $listTableDuplicate = $this->db
            ->select("{$this->map->table()->Name},COUNT({$this->map->table()->Name}) AS count")
            ->having('count > 1')
            ->group_by($this->map->table()->Name)
            ->get($this->map->table)
            ->result_array($this->map->table()->Name);
        foreach ($listTableDuplicate as $item) {
            $this->db
                ->where_in($this->map->table()->ID, $this->db
                    ->select($this->map->table()->ID)
                    ->where($this->map->table()->Name, $item)
                    ->order_by($this->map->table()->ID, 'ASC')
                    ->limit(18446744073709551615, 1)
                    ->get($this->map->table)
                    ->result_array($this->map->table()->ID))
                ->delete($this->map->table);
        }
        $listFieldDuplicate = $this->db
            ->select("{$this->map->field()->Name},{$this->map->field()->TableID},COUNT({$this->map->field()->ID}) as count")
            ->having('count > 1')
            ->group_by(array($this->map->field()->Name, $this->map->field()->TableID))
            ->get($this->map->field)
            ->result_array();
        foreach ($listFieldDuplicate as $field) {
            $this->db
                ->where_in($this->map->field()->ID, $this->db
                    ->select($this->map->field()->ID)
                    ->where(array(
                        $this->map->field()->Name => $field[$this->map->field()->Name],
                        $this->map->field()->TableID => $field[$this->map->field()->TableID]
                    ))
                    ->order_by($this->map->field()->ID, 'ASC')
                    ->limit(18446744073709551615, 1)
                    ->get($this->map->field)
                    ->result_array($this->map->field()->ID))
                ->delete($this->map->field);
        }
        $index = 0;
        foreach ($listTable as $table) {
            if ($this->db->table_exists($this->map->table)) {
                if ($this->db->get_where($this->map->table, array($this->map->table()->Name => $table))->num_rows() == 0) {
                    $this->db->insert($this->map->table, array(
                        $this->map->table()->Name => $table,
                        $this->map->table()->DisplayName => $table,
                        $this->map->table()->Description => '',
                        $this->map->table()->Order => $index,
                        $this->map->table()->Created => time(),
                        $this->map->table()->UserID => $userId,
                        $this->map->table()->Activated => 1,
                        $this->map->table()->Deleted => 0,
                    ));
                }
                $index++;
                if ($this->db->table_exists($this->map->field)) {
                    $tableID = $this->db
                        ->select($this->map->table()->ID)
                        ->where(array($this->map->table()->Name => $table))
                        ->get($this->map->table, 1, 0)
                        ->result_array();

                    if (count($tableID) == 1) {
                        $tableID = $tableID[0][$this->map->table()->ID];
                        $field_data = $this->db->field_data($table);
                        if (!is_array($field_data)) continue;
                        $this->db->where($this->map->field()->TableID, $tableID)->where_not_in($this->map->field()->Name, array_keys($field_data))->delete($this->map->field);
                        $index2 = 0;
                        foreach ($field_data as $fName => $f) {
                            if ($this->db->get_where($this->map->field, array($this->map->field()->Name => $fName, $this->map->field()->TableID => $tableID))->num_rows() == 0) {
                                $this->db->insert($this->map->field, array(
                                    $this->map->field()->Name => $fName,
                                    $this->map->field()->DisplayName => $fName,
                                    $this->map->field()->Description => $fName,
                                    $this->map->field()->TableID => $tableID,
                                    $this->map->field()->Type => $f['type'],
                                    $this->map->field()->Constraint => $f['constraint'],
                                    $this->map->field()->Default => $f['default'],
                                    $this->map->field()->Unsigned => $f['unsigned'],
                                    $this->map->field()->Null => $f['null'],
                                    $this->map->field()->Auto_increment => $f['auto_increment'],
                                    $this->map->field()->TypeInputID => 1,
                                    $this->map->field()->TypeOutputID => 1,
                                    $this->map->field()->Order => $index2,
                                    $this->map->field()->Created => time(),
                                    $this->map->field()->UserID => $userId,
                                    $this->map->field()->Activated => 1,
                                    $this->map->field()->Deleted => 0
                                ));
                            } else {
                                $this->db->update($this->map->field, array(
                                        $this->map->field()->Type => $f['type'],
                                        $this->map->field()->Constraint => $f['constraint'],
                                        $this->map->field()->Default => $f['default'],
                                        $this->map->field()->Unsigned => $f['unsigned'],
                                        $this->map->field()->Null => $f['null'],
                                        $this->map->field()->Auto_increment => $f['auto_increment'],
                                    ), array(
                                        $this->map->field()->Name => $fName,
                                        $this->map->field()->TableID => $tableID,
                                    )
                                );
                            }
                            $index2++;
                        }
                    }
                }
            }
        }
        if ($this->db->table_exists($this->map->table)) {
            $listTableIDQuery = $this->db->select($this->map->table()->ID)->get($this->map->table)->result_array();
            $listTableID = array();
            foreach ($listTableIDQuery as $item) {
                $listTableID[] = $item[$this->map->table()->ID];
            }
            $this->db->where_not_in($this->map->field()->TableID, $listTableID)->delete($this->map->field);
        }

    }
}